<?php

namespace common\tools;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Desc EXCEL工具类
 * * */
class Excel {
    
    /**
     * 导入EXCEL
     * @param file $files 文件流
     * **/
   public function Import($file) {
        header("content-type:text/html;charset=utf-8");
        try {
            
            $spreadsheet  = IOFactory::load($file->tempName);
            $data = $spreadsheet->getSheet(0)->toArray();
            if(empty($data)){
                return [];
            }
            unset($data[0]);
            return array_values($data);
        } catch (\Exception $exc) {
            return [];
        }
    }

    /**
     * 导出CSV
     * @param array $header ['字段'=>'标题']
     * @param array $data ['字段'=>'值']
     * @param string $title 表格名称
     * * */
    public function exportCsv($header, $data, $title = '') {
        try {
            set_time_limit(0);
            if (empty($header) || empty($data)) {
                throw new \Exception('参数异常');
            }
            
            $spreadsheet = new Spreadsheet();
            //工作表
            $sheet = $spreadsheet->getActiveSheet();
            //默认单元格宽度
            $sheet->getDefaultColumnDimension()->setWidth(40);
            
            //设置sheet标题
            $sheet->setTitle($title);
            
            //设置第一栏的标题
            $head_line = array_values($header);
            foreach ($head_line as $index => $item) {
                $char = Coordinate::stringFromColumnIndex($index + 1);
                $sheet->setCellValue($char . "1", $item);
                $sheet->getColumnDimension($char)->setWidth(40);

            }

            //设置内容
            $list = $this->getExportData($header, $data);
            foreach ($list as $key=>$values) {
                $key = $key +2;
                $values = array_values($values);
                foreach ($values as $k => $v) {
                    $char = Coordinate::stringFromColumnIndex($k + 1);
                    $sheet->setCellValue($char . $key, $v);
                    $sheet->getColumnDimension($char)->setAutoSize(true);
                }
            }
            
            $export_name = $title?$title:date('YmdHis');
            $writer = new Csv($spreadsheet);
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //告诉浏览器输出07Excel文件
            header("Content-Disposition: attachment;filename={$export_name}.csv"); //告诉浏览器输出浏览器名称
            header('Cache-Control: max-age=0'); //禁止缓存
            $writer->save('php://output');
            return '';
        } catch (\Exception $exc) {
            return $exc->getMessage();
        }
    }

    /**
     * 导出数据处理
     * @param array $title ['字段'=>'标题']
     * @param array $data ['字段'=>'值']
     * * */
    public function getExportData($title, $data) {

        $export_arr = [];
        foreach ($data as $val) {
            $item = [];
            foreach ($title as $field => $value) {
                $item[] = isset($val[$field]) ? $val[$field] : '';
            }
            $export_arr[] = $item;
        }
        unset($data);
        return $export_arr;
    }

}
